package com.avic.szzx.service;

import com.avic.szzx.controller.bean.quality_tracing.EquipmentInformationCorrespondence;
import com.avic.szzx.controller.bean.quality_tracing.ProcessRecord;
import com.avic.szzx.controller.bean.quality_tracing.relationship.EquipmentProcessRelationship;
import com.fasterxml.jackson.core.JsonProcessingException;
import com.fasterxml.jackson.databind.ObjectMapper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.stereotype.Service;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Slf4j
@Service
public class TaskService {
    @Autowired
    private JdbcTemplate jdbcTemplate;


    /**
     * 新增装备数据到关系型数据库
     *
     * @param data
     * @return
     */
    public int insertEquipmentInformationCorrespondenceData(EquipmentInformationCorrespondence data) {
        String sql = "INSERT INTO equipment_information_correspondence(id,name,processing_manufacturing_unit,org_identity,created_by,creation_date,last_updated_by,last_updated_date,last_updated_ip,version) VALUES (?,?,?,?,?,?,?,?,?,?)";
        log.info("开始执行关系型数据库实例数据插入操作，sql is：[{}]", sql);
        return jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement preparedStatement) throws SQLException {
                preparedStatement.setString(1, data.getId());
                preparedStatement.setString(2, data.getName());
                preparedStatement.setString(3, data.getProcessing_manufacturing_unit());
                preparedStatement.setString(4, data.getOrg_identity());
                preparedStatement.setString(5, String.valueOf(new Date().getTime()));
                preparedStatement.setString(6, data.getCreation_date());
                preparedStatement.setString(7, data.getLast_updated_by());
                preparedStatement.setString(8, data.getLast_updated_date());
                preparedStatement.setString(9, data.getLast_updated_ip());
                preparedStatement.setString(10, data.getVersion());
            }
        });
    }

    /**
     * 插入加工记录信息到关系型数据库
     *
     * @param data
     * @return
     */
    public int insertProcessRecordata(ProcessRecord data) {
        String sql = "INSERT INTO processing_record(id,process_document,process_name,org_identity,created_by,creation_date,last_updated_by,last_updated_date,last_updated_ip,version) VALUES (?,?,?,?,?,?,?,?,?,?)";
        log.info("开始执行关系型数据库实例数据插入操作，sql is：[{}]", sql);
        return jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement preparedStatement) throws SQLException {
                preparedStatement.setString(1, data.getId());
                preparedStatement.setString(2, data.getProcess_document());
                preparedStatement.setString(3, data.getProcess_name());
                preparedStatement.setString(4, data.getOrg_identity());
                preparedStatement.setString(5, data.getCreated_by());
                preparedStatement.setString(6, String.valueOf(new Date().getTime()));
                preparedStatement.setString(7, data.getLast_updated_by());
                preparedStatement.setString(8, data.getLast_updated_date());
                preparedStatement.setString(9, data.getLast_updated_ip());
                preparedStatement.setString(10, data.getVersion());
            }
        });
    }

    /**
     * 更新装备信息
     *
     * @param data
     * @return
     */
    public int updateEquipmentInformationCorrespondenceData(EquipmentInformationCorrespondence data) {
        String sql = "UPDATE equipment_information_correspondence SET name= ?,processing_manufacturing_unit=?, org_identity = ?, created_by = ?, creation_date=?, last_updated_by=?, last_updated_date=?, last_updated_ip=?, version=? where id = ?";
        log.info("开始执行关系型数据库实例数据更新操作，sql is：[{}]", sql);
        return jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement preparedStatement) throws SQLException {
                preparedStatement.setString(1, data.getName());
                preparedStatement.setString(2, data.getProcessing_manufacturing_unit());
                preparedStatement.setString(3, data.getOrg_identity());
                preparedStatement.setString(4, data.getCreated_by());
                preparedStatement.setString(5, String.valueOf(new Date().getTime()));
                preparedStatement.setString(6, data.getLast_updated_by());
                preparedStatement.setString(7, data.getLast_updated_date());
                preparedStatement.setString(8, data.getLast_updated_ip());
                preparedStatement.setString(9, data.getVersion());
                preparedStatement.setString(10, data.getId());
            }
        });
    }

    /**
     * 插入装备和加工记录关系数据到关系型数据库
     *
     * @param data
     * @return
     */
    public int insertEquipmentProcessRelationshipData(EquipmentProcessRelationship data) {
        String sql = "INSERT INTO equipment_process_relationship(id,equipment_lot_number,process_number,org_identity,created_by,creation_date,last_updated_by,last_updated_date,last_updated_ip,version) VALUES (?,?,?,?,?,?,?,?,?,?)";
        log.info("开始执行关系型数据库关系数据新增操作，sql is：[{}]", sql);
        return jdbcTemplate.update(sql, new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement preparedStatement) throws SQLException {
                preparedStatement.setString(1, data.getId());
                preparedStatement.setString(2, data.getEquipment_lot_number());
                preparedStatement.setString(3, data.getProcess_number());
                preparedStatement.setString(4, data.getOrg_identity());
                preparedStatement.setString(5, data.getCreated_by());
                preparedStatement.setString(6, String.valueOf(new Date().getTime()));
                preparedStatement.setString(7, data.getLast_updated_by());
                preparedStatement.setString(8, data.getLast_updated_date());
                preparedStatement.setString(9, data.getLast_updated_ip());
                preparedStatement.setString(10, data.getVersion());
            }
        });
    }

    /**
     * 获取指定table的数据
     *
     * @param table
     * @return
     */
    public List<Map<String, Object>> getTableData(String table) {
        String sql = "SELECT * FROM " + table;
        return jdbcTemplate.queryForList(sql);
    }

    /**
     * rollback指定id的数据
     *
     * @param table_name
     * @param id
     * @return
     */
    public int rollbackData(String table_name, String id) {
        return deleteData(table_name, id);
    }

    /**
     * 删除指定id的数据
     *
     * @param table_name
     * @param id
     * @return
     */
    public int deleteData(String table_name, String id) {
        StringBuffer sql = new StringBuffer();
        sql.append("DELETE FROM ").append(table_name).append(" WHERE id = \"").append(id).append("\";");
        log.info("开始执行关系型数据库数据删除操作，sql is：[{}]", sql);
        return jdbcTemplate.update(sql.toString());
    }

    /**
     * 获取测试数据
     *
     * @return
     */
    public String getData() {
        String sql = "SELECT * FROM avic_cargo_location_information";
        String value = "";
        ObjectMapper objectMapper = new ObjectMapper();
        List<Map<String, Object>> maps = jdbcTemplate.queryForList(sql);
        try {
            value = objectMapper.writeValueAsString(maps);
        } catch (JsonProcessingException e) {
            throw new RuntimeException(e);
        }
        return value;
    }
}
